/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAOClass;

import java.sql.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Phong
 */
public class Members extends Connect {

    private String userName;
    private String passWord;
    private Statement sqlQuerry;
    private PreparedStatement sql;
    private ResultSet result;
    private Connection cnn = null;
    ArrayList<InfoMember> listMembers;

    public Members() {
        super();
    }

    public String getPassWord() {
        return passWord;
    }

    public void setPassWord(String passWord) {
        this.passWord = passWord;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public void closeConnect() {
        try {
            if (cnn != null) {
                cnn.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    //method login for all    
    public boolean checkSameMember(String userName) {
        boolean flag = true;
        cnn = getConnect();

        try {
            sql = cnn.prepareStatement("SELECT * FROM Members WHERE UserName = ?");
            sql.setString(1, userName);
            result = sql.executeQuery();
            if (result.next()) {
                flag = false;
                return flag;
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            closeConnect();
        }

        return flag;
    }

    public boolean checkSameEmail(String mail) {
        boolean flag = true;
        cnn = getConnect();

        try {
            sql = cnn.prepareStatement("SELECT Email FROM Members WHERE Email = ?");
            sql.setString(1, mail);
            result = sql.executeQuery();
            if (result.next()) {
                flag = false;
                return flag;
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            closeConnect();
        }

        return flag;
    }

    public static void main(String[] args) {
        boolean a = new Members().validateMember("root", "root");
        boolean b = new Members().validateAdmin("root");
        System.out.println(a);
        System.out.println(b);
    }

    public boolean validateMember(String u, String pass) {
        boolean flag = true;
        cnn = getConnect();
        try {
            sql = cnn.prepareStatement("SELECT * FROM Members WHERE UserName = ? AND Status = 1");
            sql.setString(1, u.replace("'", "abc"));
            result = sql.executeQuery();
            if (result.next()) {
                sql = cnn.prepareStatement("SELECT * FROM Members WHERE Password = ?");
                sql.setString(1, pass.replace("'", "def"));
                result = sql.executeQuery();
                if (result.next()) {
                    return flag;
                } else {
                    flag = false;
                }
            } else {
                flag = false;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            closeConnect();
        }
        return flag;
    }

    public boolean validateAdmin(String adminUser) {
        boolean flag = false;

        try {

            cnn = this.getConnect();
            sql = cnn.prepareStatement("SELECT IDG FROM Members WHERE UserName = ?");
            sql.setString(1, adminUser);
            result = sql.executeQuery();
            while (result.next()) {
                if (result.getInt("IDG") == 1) {
                    flag = true;
                    return flag;
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(Members.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeConnect();
        }
        return flag;
    }

    public int AdminCHM(String adminUser) {
        int flag = 0;

        try {

            cnn = this.getConnect();
            sql = cnn.prepareStatement("SELECT IDG FROM Members WHERE UserName = ?");
            sql.setString(1, adminUser);
            result = sql.executeQuery();
            while (result.next()) {
                if (result.getInt("IDG") == 1) {
                    flag = 1;
                }
                if (result.getInt("IDG") == 1) {
                    flag = 2;
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(Members.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeConnect();
        }
        return flag;
    }

    //method singin for all user
    public boolean Singin(String user, int IDG, String pass, String name, int sex, String birth, String tel, String add, String mail, int status) {
        boolean flag = true;
        cnn = getConnect();
        try {
            sql = cnn.prepareStatement("EXEC MemberInsert ?,?,?,?,?,?,?,?,?,?");
            sql.setString(1, user);
            sql.setInt(2, IDG);
            sql.setString(3, pass);
            sql.setString(4, name);
            sql.setInt(5, sex);
            sql.setString(6, birth);
            sql.setString(7, tel);
            sql.setString(8, add);
            sql.setString(9, mail);
            sql.setInt(10, status);
            if (sql.executeUpdate() == 1) {
                return flag;
            } else {
                flag = false;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            closeConnect();
        }
        return flag;
    }

    //method Edit member
    public boolean editMember(String user, int IDG, String pass, String name, int sex, String birth, String tel, String add, String mail, int status) {
        boolean flag = true;
        cnn = getConnect();
        try {
            sql = cnn.prepareStatement("EXEC Memberupdate ?,?,?,?,?,?,?,?,?,?");
            sql.setString(1, user);
            sql.setInt(2, IDG);
            sql.setString(3, pass);
            sql.setString(4, name);
            sql.setInt(5, sex);
            sql.setString(6, birth);
            sql.setString(7, tel);
            sql.setString(8, add);
            sql.setString(9, mail);
            sql.setInt(10, status);
            if (sql.executeUpdate() == 1) {
                return flag;
            } else {
                flag = false;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            closeConnect();
        }
        return flag;
    }

    public boolean blockMember(String mem, int statusMem) {
        boolean flag = false;
        String updateQuerry = "";
        try {
            cnn = this.getConnect();
            if (statusMem == 1) {
                updateQuerry = "UPDATE Members SET Status = 0 WHERE UserName = ?";
            } else {
                updateQuerry = "UPDATE Members SET Status = 1 WHERE UserName = ?";
            }
            sql = cnn.prepareStatement(updateQuerry);
            sql.setString(1, mem);
            sql.executeUpdate();
            flag = true;
            return flag;
        } catch (SQLException ex) {
            Logger.getLogger(Members.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeConnect();
        }
        return flag;
    }

    public boolean checkStatus(String user) {
        boolean flag = false;

        return flag;

    }
    //method Delete member
/*public boolean delMember(String user) {
    boolean flag = true;
    cnn = getConnect();
    try {
    sql = cnn.prepareStatement("EXEC MemberDelete ?");
    sql.setString(1, user);
    if (checkSameMember(user)) {
    sql.executeUpdate();
    } else {
    flag = false;
    }
    } catch (SQLException ex) {
    Logger.getLogger(Members.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
    closeConnect();
    }
    return flag;
    }*/

    public ArrayList<InfoMember> getMembers() {
        cnn = getConnect();
        listMembers = new ArrayList<InfoMember>();
        try {
            sqlQuerry = cnn.createStatement();
            result = sqlQuerry.executeQuery("SELECT * FROM Members ORDER BY Status");
            while (result.next()) {
                String user = result.getString("UserName");
                int group = result.getInt("IDG");
                String pass = result.getString("Password");
                String name = result.getString("FullName");
                int sex = result.getInt("Sex");
                String birthday = result.getString("Birthday");
                String tel = result.getString("Telephone");
                String add = result.getString("Address");
                String mail = result.getString("EMail");
                int status = result.getInt("Status");

                listMembers.add(new InfoMember(user, group, pass, name, sex, birthday, tel, add, mail, status));

            }
        } catch (SQLException ex) {
            Logger.getLogger(Members.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeConnect();
        }
        return listMembers;
    }

    public ArrayList<InfoMember> getMembersByName(String userName) {
        cnn = getConnect();
        ArrayList<InfoMember> listMem = new ArrayList<InfoMember>();
        try {
            sql = cnn.prepareStatement("SELECT * FROM Members WHERE UserName = ?");
            sql.setString(1, userName);
            result = sql.executeQuery();
            while (result.next()) {
                String user = result.getString("UserName");
                int group = result.getInt("IDG");
                String pass = result.getString("Password");
                String name = result.getString("FullName");
                int sex = result.getInt("Sex");
                String birthday = result.getString("Birthday");
                String tel = result.getString("Telephone");
                String add = result.getString("Address");
                String mail = result.getString("EMail");
                int status = result.getInt("Status");

                listMem.add(new InfoMember(user, group, pass, name, sex, birthday, tel, add, mail, status));

            }
        } catch (SQLException ex) {
            Logger.getLogger(Members.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeConnect();
        }
        return listMem;
    }

    /*public static void main(String[] args) {
    Members obj = new Members();
    System.out.println(obj.validateAdmin("phongtn"));
    }
    public ResultSet selectTopMember (){
    ResultSet rs  =null;
    cnn = getConnect();
    try{
    Statement s = cnn.createStatement();
    rs = s.executeQuery("select top 5 * from Result order by Mark Desc");
    }catch(Exception e){
    e.printStackTrace();
    }
    return rs;
    }*/
}
